package org.compiere.report;

import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.compiere.model.MAccount;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MElementValue;
import org.compiere.model.MLocator;
import org.compiere.model.MOrg;
import org.compiere.model.X_A_Asset;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;


public class A_FA_Report extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;
	/** Period Parameter */
	private Timestamp p_Date_From = null;
	private Timestamp p_Date_To = null;
	private int p_A_Asset_ID= 0;
	/** Period Parameter */

/*	private WritableFont times10ptNoBold;
	private WritableCellFormat cellstyle;
	private WritableCellFormat vsNormal;	
	private WritableCellFormat vsNumber;
	private WritableCellFormat vsText;*/
	
	String getSQlresult = null;

	/** */
	private String file = "";

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("DateFrom")) 
				p_Date_From = (Timestamp) para[i].getParameter();
			else if (name.equals("DateTo")) 
				p_Date_To = (Timestamp) para[i].getParameter();
			else if (name.equals("A_Asset_ID")) 
				p_A_Asset_ID = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		// / here another code)
	}

	/**
     * Create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short)18);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);

        Font monthFont = wb.createFont();
        monthFont.setFontHeightInPoints((short)11);
        monthFont.setColor(IndexedColors.WHITE.getIndex());
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(monthFont);
        style.setWrapText(true);
        styles.put("header", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setWrapText(true);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setWrapText(false);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cellT", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setWrapText(false);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("#,##0.00"));
        styles.put("cellN", style);
        
        
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
        styles.put("formula", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        //style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
        styles.put("text", style);

        return styles;
    }
    
	@Override
	protected String doIt() throws Exception {

		file = Util.localFilePath + "/Form_OS2.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));
		Workbook wb = new HSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Map<String, CellStyle> s = createStyles(wb);
		Cell c = null;

		Timestamp currdate = new Timestamp(System.currentTimeMillis());
		SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
		String str = dateFormat.format(currdate);
		
		MOrg org = MOrg.get(m_ctx,Env.getAD_Client_ID(m_ctx));
		MBPartner bpartner = MBPartner.get(m_ctx, org.getLinkedC_BPartner_ID(get_TrxName()));
		str=bpartner.getDescription();
		Row R = sheet.getRow(1);
        c = R.getCell(1); c.setCellStyle(s.get("text")); c.setCellValue(str); 
		
		str = "Период: "+dateFormat.format(p_Date_From)+" - "+
				dateFormat.format(p_Date_To);
		R = sheet.createRow(2);
        c = R.createCell(1); c.setCellStyle(s.get("text")); c.setCellValue(str); 
		//sheet.addCell(new Label(64, 10, bpartner.get_Value("FI_TIN").toString(), cellstyle));			
		//sheet.addCell(new Label(24, 10, org.getName(), vsNormal));			

		//sheet.addCell(new Label(0, 25, "1", cellstyle));

		int n=1, r=6;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT A_Asset_ID "
				+ "FROM A_Asset "
				+ "WHERE AD_Client_ID=" + Env.getAD_Client_ID(m_ctx);
		if (p_A_Asset_ID>0) 
			sql=sql+" AND A_Asset_ID = "+p_A_Asset_ID;
		sql=sql+ " AND IsActive = 'Y'"		
				+ " ORDER BY Name";		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				X_A_Asset asset = new X_A_Asset (getCtx(), rs.getInt(1), get_TrxName());
			    BigDecimal bCostTo = asset.getAssetCost(p_Date_To);
			    BigDecimal bCostFrom = asset.getAssetCost(p_Date_From);
		        BigDecimal bAccumDepTo = asset.getAccumDepreciation(p_Date_To);
		        BigDecimal bAccumDepFrom = asset.getAccumDepreciation(p_Date_From);
			    BigDecimal bCostForDepTo = asset.getAssetDepForCost(p_Date_To);
			    BigDecimal bCostDec = new BigDecimal(0.0);
			    BigDecimal bCostInc = new BigDecimal(0.0);
			    if (bCostTo.compareTo(bCostFrom)>0)
			    	bCostInc=bCostTo.subtract(bCostFrom);
			    else	
			    	bCostDec=bCostTo.subtract(bCostFrom);
		        BigDecimal bDep = asset.getDepPost(p_Date_From, p_Date_To);
		        BigDecimal bDepCost = bCostTo.subtract(bAccumDepTo);
			    if (   (bCostTo.compareTo(BigDecimal.ZERO)==0)
			    	 &&(bCostFrom.compareTo(BigDecimal.ZERO)==0)
			    	 &&(bAccumDepFrom.compareTo(BigDecimal.ZERO)==0)
			    	 &&(bCostInc.compareTo(BigDecimal.ZERO)==0)
			    	 &&(bDep.compareTo(BigDecimal.ZERO)==0)
			    	 &&(bCostDec.compareTo(BigDecimal.ZERO)==0)
			    	 &&(bCostTo.compareTo(BigDecimal.ZERO)==0)
			    	 &&(bCostDec.compareTo(BigDecimal.ZERO)==0)
			    	 &&(bAccumDepTo.compareTo(BigDecimal.ZERO)==0)
			    	 &&(bDepCost.compareTo(BigDecimal.ZERO)==0)) continue;
		        
				R = sheet.createRow(r);
		        //Row.setHeightInPoints(25);
		        c = R.createCell(0); c.setCellStyle(s.get("cell" )); c.setCellValue(n); 
		        c = R.createCell(1); c.setCellStyle(s.get("cellT")); c.setCellValue(asset.getName()); 
		        c = R.createCell(2); c.setCellStyle(s.get("cell" )); c.setCellValue(asset.getValue()); 
		        c = R.createCell(3); c.setCellStyle(s.get("cellT")); c.setCellValue("Линейный способ (от срока)");
		        c = R.createCell(4); c.setCellStyle(s.get("cell" )); c.setCellValue(asset.getAcctValue("UseLifeMonths"));
		        str="";
				if (asset.getAssetServiceDate()!=null) str=dateFormat.format(asset.getAssetServiceDate());
		        c = R.createCell(5); c.setCellStyle(s.get("cell")); c.setCellValue(str);
		        
		        str="";
		        /*int bpsr = asset.getC_BPartnerSR_ID(p_Date_To);
				if (bpsr>0) { 
					MBPartner bpartnersr  = MBPartner.get(m_ctx, bpsr);
					str = bpartnersr.getName();
				};*/
		        int ilocator = asset.getM_Locator_ID();
		        if (ilocator>0) {
		        	MLocator locator = new MLocator(getCtx(), ilocator, get_TrxName());
		        	str=locator.getValue();
		        }
			    c = R.createCell(6); c.setCellStyle(s.get("cellT" )); c.setCellValue(str);
			    
			    str="";
			    int iacct = asset.getAcctValue("A_Asset_Acct");
			    if (iacct>0) {
			    	MAccount acct = MAccount.get (getCtx(), iacct);
			    	MElementValue m_acct = new MElementValue (getCtx(), acct.getAccount_ID(), get_TrxName());
			    	str = m_acct.getValue();
			    }
			    c = R.createCell(7); c.setCellStyle(s.get("cell" )); c.setCellValue(str);
				
			    	

			    c = R.createCell(8);c.setCellStyle(s.get("cellN")); c.setCellValue(bCostTo.doubleValue());
		        c = R.createCell(9);c.setCellStyle(s.get("cellN")); c.setCellValue(bCostForDepTo.doubleValue());
		        c = R.createCell(10);c.setCellStyle(s.get("cellN")); c.setCellValue(bCostFrom.doubleValue());
				c = R.createCell(11);c.setCellStyle(s.get("cellN")); c.setCellValue(bAccumDepFrom.doubleValue());
				c = R.createCell(12);c.setCellStyle(s.get("cellN")); c.setCellValue(bCostInc.doubleValue());
				c = R.createCell(13);c.setCellStyle(s.get("cellN")); c.setCellValue(bDep.doubleValue());
				c = R.createCell(14);c.setCellStyle(s.get("cellN")); c.setCellValue(bCostDec.doubleValue());
		        c = R.createCell(15);c.setCellStyle(s.get("cellN")); c.setCellValue(bCostTo.doubleValue());
				c = R.createCell(16);c.setCellStyle(s.get("cellN")); c.setCellValue(bAccumDepTo.doubleValue());
				c = R.createCell(17);c.setCellStyle(s.get("cellN")); c.setCellValue(bDepCost.doubleValue());
				c = R.createCell(18);c.setCellStyle(s.get("cellN")); c.setCellValue(0);

				n++; r++; 
			}
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
		}
		
		
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(file);
		
		wb.write(fileOut);
		fileOut.close();
		
		try {
			//Forces Excel to use the specified file as a template for the default workbook. 
			Runtime.getRuntime().exec("cmd /c start excel " + file+" /t");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return "";
	}
}